﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Objects.DataClasses;
using System.Linq;
using System.Linq.Expressions;
using System.Web;
using System.Data.Objects;


namespace DoveSiButta.Models
{

    public class BoxRepository : DoveSiButta.Models.IBoxRepository
    {

        DoveSiButtaEntities db = new DoveSiButtaEntities();

        //
        // Query Methods

        public IQueryable<Box> FindBoxesByText(string q)
        {
            return db.Boxes.Where(d => d.Title.Contains(q)
                            || d.Description.Contains(q)
                            || d.Address.Contains(q)
                            || d.HostedBy.Contains(q));
        }

        public IQueryable<Box> FindBoxesByType(string q) //GM
        {
            return db.Boxes.Where(d => d.BoxType.Contains(q));
        }

        public IQueryable<Box> FindAllBoxes()
        {
            return db.Boxes;
        }

        public IQueryable<Box> FindUpcomingEvents()
        {
            DateTime data30 = DateTime.Now.AddDays(30);
            return from box in FindAllBoxes()
                   where box.EventDate <= data30 //giusto per prendere anche eventuali cestini creati con data da qui a un mese //GM
                   orderby box.EventDate descending //GM 2012-05-15 ticket #18
                   select box;
        }

        public IQueryable<Box> FindByLocation(float latitude, float longitude)
        {
            var boxes = from box in FindUpcomingEvents()
                        join i in NearestBoxes(latitude, longitude)
                        on box.BoxID equals i.BoxID
                        select box;

            return boxes;

            //List<Box> resultList = new List<Box>();

            //var results = db.Database.SqlQuery<Box>("SELECT * FROM Dinners WHERE EventDate >= {0} AND dbo.DistanceBetween({1}, {2}, Latitude, Longitude) < 1000", DateTime.Now, latitude, longitude);
            //return results.AsQueryable<Box>();
        }

        public Box GetBox(int id)
        {
            return db.Boxes.SingleOrDefault(d => d.BoxID == id);
        }

        //
        // Insert/Delete Methods

        public void Add(Box box)
        {
            db.Boxes.AddObject(box);
        }

        public void Delete(Box box)
        {
            foreach (Rating rsvp in box.Ratings.ToList())
                db.Ratings.DeleteObject(rsvp);
            db.Boxes.DeleteObject(box);
        }

        //
        // Persistence 

        public void Save()
        {
            db.SaveChanges();
        }


        // Helper Methods

        [EdmFunction("DoveSiButtaModel.Store", "DistanceBetween")]
        public static double DistanceBetween(double lat1, double long1, double lat2, double long2)
        {

            //return Math.Sqrt( Math.Pow((lat1 - lat2), 2 ) + Math.Pow((long1 - long2), 2));
            //ObjectParameter Latitude = new ObjectParameter( "lat1",lat1);
            //ObjectParameter Longitude = new ObjectParameter("long1", long1);
            //ObjectParameter Latitude2 = new ObjectParameter("lat2", lat2);
            //ObjectParameter Longitude2 = new ObjectParameter("long2", long2);
            //return db.ExecuteFunction("DistanceBetween", Latitude, Longitude, Latitude2, Longitude2);
            throw new NotImplementedException("Only call through LINQ expression");
            
        }

        public IQueryable<Box> NearestBoxes(double latitude, double longitude)
        {
            return from d in db.Boxes
                   where DistanceBetween(latitude, longitude, (double)d.Latitude, (double)d.Longitude) < 1 //100 //GM 2012-05-24 carico solo quelli entro 1 "grado" di lat/lon che e' piu che sufficiente
                   select d;
        } 
    }
}
